package com.doudou.casebook.db;

import static android.provider.Settings.System.getString;
import static androidx.constraintlayout.helper.widget.MotionEffect.TAG;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.content.Intent;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.util.Log;
import android.widget.Toast;

import com.doudou.casebook.R;
import com.doudou.casebook.utils.FloatUtils;

import java.util.ArrayList;
import java.util.List;

/**
 * 数据库管理类
 * 负责对数据表进行操作
 */
public class DBManager {
    private static SQLiteDatabase db;

    public static void initDB(Context context) {
        DBOpenHelper helper = new DBOpenHelper(context);//得到帮助类对象
        db = helper.getWritableDatabase();//得到数据库对象
    }

    /**
     * 读取数据库的数据,写入到内存集合
     */
    public static List<TypeBean> getTypeList(int kind) {
        List<TypeBean> list = new ArrayList<>();
        //读取typetb表中的数据
        String sql = "select * from typetb where kind=" + kind;
        Cursor cursor = db.rawQuery(sql, null);
        //循环读取游标内容,存储到对象中
        while (cursor.moveToNext()) {
            @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename"));
            @SuppressLint("Range") int imageId = cursor.getInt(cursor.getColumnIndex("imageId"));
            @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            @SuppressLint("Range") int kind1 = cursor.getInt(cursor.getColumnIndex("kind"));
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));

            TypeBean typeBean = new TypeBean(id, typename, sImageId, imageId, kind1);
            list.add(typeBean);
        }
        return list;
    }

    /**
     * 插入一条记录到记账表
     */
    public static void insertItemToAccounttb(AccountBean bean) {
        ContentValues values = new ContentValues();
        values.put("typename", bean.getTypename());
        values.put("sImageId", bean.getsImageId());
        values.put("beizhu", bean.getBeizhu());
        values.put("money", bean.getMoney());
        values.put("time", bean.getTime());
        values.put("year", bean.getYear());
        values.put("month", bean.getMonth());
        values.put("day", bean.getDay());
        values.put("kind", bean.getKind());
        db.insert("accounttb", null, values);
    }

    /**
     * 获取记账表中的某一天的数据
     */
    public static List<AccountBean> getAccountListOneDayFromAccounttb(int year, int month, int day) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=? and day=? order by id desc";
        @SuppressLint("Recycle") Cursor cursor = db.rawQuery(sql, new String[]{
                String.valueOf(year), String.valueOf(month), String.valueOf(day)
        });
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
            @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename"));
            @SuppressLint("Range") String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time"));
            @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind);

            list.add(accountBean);
        }
        return list;
    }

    /**
     * 获取记账表中的某一月的数据
     */
    public static List<AccountBean> getAccountListOneMonthFromAccounttb(int year, int month) {

        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where year=? and month=?  order by id desc";
        @SuppressLint("Recycle") Cursor cursor = db.rawQuery(sql, new String[]{
                String.valueOf(year), String.valueOf(month)
        });
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
            @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename"));
            @SuppressLint("Range") String beizhu = cursor.getString(cursor.getColumnIndex("beizhu"));
            @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time"));
            @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money"));
            @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day"));
            AccountBean accountBean = new AccountBean(id, typename, sImageId, beizhu, money, time, year, month, day, kind);

            list.add(accountBean);
        }
        return list;
    }

    /**
     * 获取某一天的收入或者支出总金额 kind 支出0,收入1
     */
    public static float getSumMoneyOneDay(int year, int month, int day, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=? and day=? and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{
                year + "", month + "", day + "", kind + ""
        });
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }

    /**
     * 获取某一月的收入或者支出总金额 kind 支出0,收入1
     */
    public static float getSumMoneyOneMonth(int year, int month, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=? and month=?  and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{
                year + "", month + "", kind + ""
        });
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }

    /**
     * 获取某一年的收入或者支出总金额 kind 支出0,收入1
     */
    public static float getSumMoneyOneYear(int year, int kind) {
        float total = 0.0f;
        String sql = "select sum(money) from accounttb where year=?   and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{
                year + "", kind + ""
        });
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            total = money;
        }

        return total;
    }
    /*统计某月分支出或者收入情况有多少条 收入-1,支出-0*/
    public static int getCountItemOneMonth(int year,int month,int kind){
        int total=0;
        String sql="select count(money) from accounttb where year=? and month=?   and kind=?";
        Cursor cursor = db.rawQuery(sql, new String[]{
                year + "",month + "", kind + ""
        });
        if (cursor.moveToFirst()) {
           @SuppressLint("Range") int count = cursor.getInt(cursor.getColumnIndex("count(money)"));
            total = count;
        }
        return total;
    }

    /**
     * 根据id删除一条记录
     */
    public static int deleteItemFromAccountById(int id) {
        int i = db.delete("accounttb", "id=?", new String[]{id + ""});
        return i;
    }

    /**
     * 根据备注搜索收入或者支出的列表
     */
    public static List<AccountBean> getAccountListBybeizhu(String beizhu) {
        List<AccountBean> list = new ArrayList<>();
        String sql = "select * from accounttb where beizhu like '%" + beizhu + "%'";
        Cursor cursor = db.rawQuery(sql, null);
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int id = cursor.getInt(cursor.getColumnIndex("id"));
            @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename"));
            @SuppressLint("Range") String bz = cursor.getString(cursor.getColumnIndex("beizhu"));
            @SuppressLint("Range") String time = cursor.getString(cursor.getColumnIndex("time"));
            @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            @SuppressLint("Range") int kind = cursor.getInt(cursor.getColumnIndex("kind"));
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("money"));
            @SuppressLint("Range") int year = cursor.getInt(cursor.getColumnIndex("year"));
            @SuppressLint("Range") int month = cursor.getInt(cursor.getColumnIndex("month"));
            @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day"));

            AccountBean accountBean = new AccountBean(id, typename, sImageId, bz, money, time, year, month, day, kind);

            list.add(accountBean);
        }

        return list;
    }

    /**
     * 查询记账表中有几个年份信息
     */
    public static List<Integer> getYearListFromAccounttb(){
        List<Integer>list  =new ArrayList<>();
        String sql = "select distinct(year) from accounttb order by year asc";
        Cursor cursor = db.rawQuery(sql,null);
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int year = cursor.getInt(cursor.getColumnIndex("year"));
            Log.e(TAG, "getYearListFromAccounttb: "+year );
            list.add(year);
        }
        return list;
    }
    /**
     * 根据年份查询有记录的月份
     */
    public static List<String> getMonthOfYearListFromAccounttb(int year){
        List<String>list  =new ArrayList<>();
        String sql = "select distinct(month) from accounttb where year=? order by month asc";
        Cursor cursor = db.rawQuery(sql,new String[]{
                year+""
        });
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int month = cursor.getInt(cursor.getColumnIndex("month"));

            list.add(String.valueOf(month));
        }

        return list;
    }

    //删除所有数据
    public static void deleteAllAccounttb() {
        String sql = "delete from accounttb";
        db.execSQL(sql);
    }

    /**
     * 查询指定年份和月份的收入或者支出每一种类型的总钱数
     * */
    public static List<ChartItemBean>getChartListFromAccounttb(int year,int month,int kind){
        List<ChartItemBean>list = new ArrayList<>();
        float sumMoneyOneMonth = getSumMoneyOneMonth(year, month, kind);  //求出支出或者收入总钱数
        String sql = "select typename,sImageId,sum(money)as total from accounttb where year=? and month=? and kind=? group by typename " +
                "order by total desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int sImageId = cursor.getInt(cursor.getColumnIndex("sImageId"));
            @SuppressLint("Range") String typename = cursor.getString(cursor.getColumnIndex("typename"));
            @SuppressLint("Range") float total = cursor.getFloat(cursor.getColumnIndex("total"));
            //计算所占百分比  total /sumMonth
            float ratio = FloatUtils.div(total,sumMoneyOneMonth);
            ChartItemBean bean = new ChartItemBean(sImageId, typename, ratio, total);
            list.add(bean);
        }
        return list;
    }

    /**
     * 获取这个月当中某一天收入支出最大的金额，金额是多少
     * */
    public static float getMaxMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select sum(money) from accounttb where year=? and month=? and kind=? group by day order by sum(money) desc";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        if (cursor.moveToFirst()) {
            @SuppressLint("Range") float money = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            return money;
        }
        return 0;
    }

    /** 根据指定月份每一日收入或者支出的总钱数的集合*/
    public static List<BarChartItemBean>getSumMoneyOneDayInMonth(int year,int month,int kind){
        String sql = "select day,sum(money) from accounttb where year=? and month=? and kind=? group by day";
        Cursor cursor = db.rawQuery(sql, new String[]{year + "", month + "", kind + ""});
        List<BarChartItemBean>list = new ArrayList<>();
        while (cursor.moveToNext()) {
            @SuppressLint("Range") int day = cursor.getInt(cursor.getColumnIndex("day"));
            @SuppressLint("Range") float smoney = cursor.getFloat(cursor.getColumnIndex("sum(money)"));
            BarChartItemBean itemBean = new BarChartItemBean(year, month, day, smoney);
            list.add(itemBean);
        }
        return list;
    }
}
